import xlrd
import re
from py2neo import Graph, Node, Relationship, NodeMatcher
from py2neo.matching import RelationshipMatcher
beans = ['菜豆', '豇豆', '毛豆', '豌豆', '扁豆', '蚕豆', '黑豆', '绿豆']  # 杂豆名称
diseases = []  # 病害
diseases_new = []  # 病害（去重后）
insects = []  # 虫害
insects_new = []  # 虫害（去重后）
pathogens = []  # 病原物
pathogens_new = []  # 病原物（去重后）

# 读取杂豆病虫害数据
def ReadExcel():
    # 打开文件
    excel = xlrd.open_workbook("D:\study\毕设\data\杂豆病虫害.xls")
    # 获取工作薄
    sheet = excel.sheet_by_index(0)
    # 获取表的行列数
    rows=sheet.nrows
    cols=sheet.ncols
    # 获取表中数值
    for i in range(1,rows):
        if "病害" in str(sheet.cell(i,0)):
            d={'CN':'','EN':'','AN':'','Brief':'','Symptom':'','Reason':'','Method':''}
            d['CN']=str(sheet.cell(i,1))[6:-1]
            d['EN']=str(sheet.cell(i,2))[6:-1]
            if d['EN']=="'":
                d['EN']=''
            d['AN']=str(sheet.cell(i,3))[6:-1]
            if d['AN']=="'":
                d['AN']=''
            d['Brief'] = str(sheet.cell(i, 4))[6:-1]
            if d['Brief']=="'":
                d['Brief']=''
            d['Symptom'] = str(sheet.cell(i, 5))[6:-1]
            if d['Symptom']=="'":
                d['Symptom']=''
            d['Reason'] = str(sheet.cell(i, 8))[6:-1].replace(' ','')
            if d['Reason']=="'":
                d['Reason']=''
            d['Method'] = str(sheet.cell(i, 11))[6:-1].replace(' ','')
            if d['Method']=="'":
                d['Method']=''
            diseases.append(d)
            if "：" in str(sheet.cell(i,6)): # 多种病原物
                np=(str(sheet.cell(i,6)).split('：')[1]).split('、')
                for j in np:
                    p = {'CN': '', 'EN': '', 'ABBR': '','Infect':''}
                    p['CN']=(re.sub("[A-Za-z0-9,(,),']", "", j)).split(' ')[0]  # 提取病原物中文名称
                    p['Infect']= str(sheet.cell(i, 7))[6:-1]
                    if "(" in j: # 病原物包含简写
                        p['ABBR']=j.split('(')[1][:-1]
                        if ")" in p['ABBR']:
                            p['ABBR']=p['ABBR'][:-1]
                    if len(p['ABBR'])!=0:
                        p['EN']=''.join(re.findall(r'[A-Za-z, ]', j))[:-len(p['ABBR'])]  # 提取病原物英文名称
                    else:
                        p['EN'] = ''.join(re.findall(r'[A-Za-z, ]', j))
                    pathogens.append(p)
            else: # 单一病原物
                p = {'CN': '', 'EN': '', 'ABBR': '','Infect':''}
                p['CN']=(re.sub("[A-Za-z0-9,(,),']", "", str(sheet.cell(i,6))[9:-1])).split(' ')[0]
                p['Infect'] = str(sheet.cell(i, 7))[6:-1]
                if "(" in (str(sheet.cell(i,6))[9:-1]):  # 病原物包含简写
                    p['ABBR'] = (str(sheet.cell(i,6))[9:-1]).split('(')[1][:-1]
                    if ")" in p['ABBR']:
                        p['ABBR'] = p['ABBR'][:-1]
                if len(p['ABBR']) != 0:
                    p['EN'] = ''.join(re.findall(r'[A-Za-z, ]', str(sheet.cell(i,6))[9:-1]))[:-len(p['ABBR'])]  # 提取病原物英文名称
                else:
                    p['EN'] = ''.join(re.findall(r'[A-Za-z, ]', str(sheet.cell(i, 6))[9:-1]))
                pathogens.append(p)
        elif "虫害" in str(sheet.cell(i,0)):
            I={'CN':'','EN':'','AN':'','Brief':'','Symptom':'','Feature':'','Lifestyle':'','Reason':'','Method':''}
            I['CN'] = str(sheet.cell(i, 1))[6:-1]
            I['EN'] = str(sheet.cell(i, 2))[6:-1]
            if I['EN'] == "'":
                I['EN'] = ''
            I['AN'] = str(sheet.cell(i, 3))[6:-1]
            if I['AN'] == "'":
                I['AN'] = ''
            I['Brief'] = str(sheet.cell(i, 4))[6:-1]
            if I['Brief'] == "'":
                I['Brief'] = ''
            I['Symptom'] = str(sheet.cell(i, 5))[6:-1]
            if I['Symptom'] == "'":
                I['Symptom'] = ''
            I['Reason'] = str(sheet.cell(i, 8))[6:-1].replace(' ','')
            if I['Reason'] == "'":
                I['Reason'] = ''
            I['Feature'] = str(sheet.cell(i, 9))[6:-1].replace(' ','')
            if I['Feature'] == "'":
                I['Feature'] = ''
            I['Lifestyle'] = str(sheet.cell(i, 10))[6:-1].replace(' ','')
            if I['Lifestyle'] == "'":
                I['Lifestyle'] = ''
            I['Method'] = str(sheet.cell(i, 11))[6:-1].replace(' ','')
            if I['Method'] == "'":
                I['Method'] = ''
            insects.append(I)
    # 去除重复值
    for i in diseases:
        flag1=0 # 标记病害是否重复
        for j in range(len(diseases_new)):
            if diseases_new[j]['CN']==i['CN']:
                flag1=1  # 病害重复
                break
        if flag1==0:
            diseases_new.append(i)
    for i in insects:
        flag2 = 0  # 标记虫害是否重复
        for j in range(len(insects_new)):
            if insects_new[j]['CN'] == i['CN']:
                flag2 = 1  # 虫害重复
                break
        if flag2 == 0:
            insects_new.append(i)
    for i in pathogens:
        flag3=0 # 标记病原物是否重复
        for j in range(len(pathogens_new)):
            if pathogens_new[j]['CN']==i['CN']:
                flag3=1  # 病原物重复
                break
        if flag3==0 and i['CN']!='':
            pathogens_new.append(i)
    print(pathogens_new)
    # print(diseases_new)
    # print(insects_new)

# 创建节点
def CreateNode(graph):
    # 创建杂豆节点
    for i in beans:
        bean_node = Node('杂豆', name=i)
        graph.create(bean_node)
    # 创建病害节点
    for i in diseases_new:
        disease_node = Node('病害', **i)
        graph.create(disease_node)
    # 创建虫害节点
    for i in insects_new:
        insect_node = Node('虫害', **i)
        graph.create(insect_node)
    # 创建病原物节点
    for i in pathogens_new:
        pathogen_node = Node('病原物', **i)
        graph.create(pathogen_node)

# 创建节点关系
def CreateRelationship(graph):
    # 打开文件
    excel = xlrd.open_workbook("D:\study\毕设\杂豆病虫害.xls")
    # 获取工作薄
    sheet = excel.sheet_by_index(0)
    # 获取表的行列数
    rows = sheet.nrows
    cols = sheet.ncols
    for i in beans:
        for j in range(1, rows):
            if i in str(sheet.cell(j, 0))[6:-1]:
                if "病害" in str(sheet.cell(j, 0))[6:-1]:
                    for k in diseases_new:
                        if str(sheet.cell(j, 1))[6:-1]== k['CN']:
                            matcher = NodeMatcher(graph)
                            nodelist_bean = list(matcher.match('杂豆', name=i))
                            nodelist_disease = list(matcher.match('病害', CN=k['CN']))
                            graph.create(Relationship(nodelist_bean[0], '易患1', nodelist_disease[0])) # 创建杂豆和病害之间的关系：易患1
                            # bean_r_dieases=Relationship(Node('杂豆', name=i), "易患1", Node('病害', **k)) 
                            # graph.create(bean_r_dieases)
                elif "虫害" in str(sheet.cell(j, 0))[6:-1]:
                    for k in insects_new:
                        if str(sheet.cell(j, 1))[6:-1]== k['CN']:
                            matcher = NodeMatcher(graph)
                            nodelist_bean = list(matcher.match('杂豆', name=i))
                            nodelist_insect = list(matcher.match('虫害', CN=k['CN']))
                            graph.create(Relationship(nodelist_bean[0], '易患2', nodelist_insect[0]))  # 创建杂豆和虫害之间的关系：易患2
                            # bean_r_insect=Relationship(Node('杂豆', name=i), "易患2", Node('虫害', **k)) 
                            # graph.create(bean_r_insect)
    for i in range(1,rows):
        for j in diseases_new:
            for k in pathogens_new:
                if j['CN']== str(sheet.cell(i,1))[6:-1] and k['CN'] in str(sheet.cell(i,6))[6:-1]:
                    matcher = NodeMatcher(graph)
                    nodelist_pathogen = list(matcher.match('病原物', CN=k['CN']))
                    nodelist_disease = list(matcher.match('病害', CN=j['CN']))
                    graph.create(Relationship(nodelist_pathogen[0], '导致', nodelist_disease[0])) # 创建病原物和病害之间的关系：导致
                    # pathogen_r_disease=Relationship(Node('病原物', **k), "导致", Node('病害', **j))
                    # graph.create(pathogen_r_disease)

if __name__ == '__main__':
    ReadExcel()
    graph = Graph("http://localhost:7474", auth=("neo4j", "zwa20010718"))
    CreateNode(graph)
    CreateRelationship(graph)